﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DTO;
using System.Data.SqlClient;
using System.Data;

namespace DAO
{
    public class DiaDiemDAO
    {

        public static List<DiaDiemDTO> getAll2(string ask)
        {
            dbprovider2DataContext db = new dbprovider2DataContext();

            var query = from n in db.diadiems
                        join m in db.taikhoans on n.taikhoan equals m.id
                        where n.status != -1
                        select new
                            {
                                id = n.id, 
                                ten = n.ten, 
                                latitude = m.tentaikhoan, 
                                longitude = m.tentaikhoan, 
                                diachi = n.diachi, 
                                linhvuc2 = n.linhvuc, 
                                taikhoan = n.taikhoan,
                            }; 

            List<DiaDiemDTO> list = new List<DiaDiemDTO>();


            var query2 = from n in query
                         join m in db.linhvucs on n.linhvuc2 equals m.id
                         where true
                         select new
                         { 
                             id = n.id, 
                             ten = n.ten,
                             latitude = n.latitude, 
                             longitude = m.tenlinhvuc, 
                             diachi = n.diachi, 
                             linhvuc2 = n.linhvuc2, 
                             taikhoan = n.taikhoan,
                         };

            foreach (var n in query2)
            {
                DiaDiemDTO info = new DiaDiemDTO();
                info.Id = n.id;
                info.Ten = n.ten;
                info.Latitude = n.latitude;
                info.Longitude = n.longitude;
                info.DiaChi = n.diachi;
                info.LinhVuc = int.Parse(n.linhvuc2.ToString());
                info.TaiKhoan = int.Parse(n.taikhoan.ToString());

                list.Add(info); 
            }
            
            return list;
        }


        #region lay tat ca dia diem
        public static List<DiaDiemDTO> getAll()
        {
            SqlConnection connect = null;
            List<DiaDiemDTO> list = new List<DiaDiemDTO>();
            try
            {
                connect = DataProvider.Connect();
                string strCommand = "select * from diadiem";
                SqlCommand command = new SqlCommand(strCommand, connect);
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    DiaDiemDTO d = new DiaDiemDTO();
                    d.Id = reader.GetInt32(0);
                    if(!reader.IsDBNull(1))
                        d.Ten = reader.GetString(1);
                    if (!reader.IsDBNull(2))
                        d.Latitude = reader.GetString(2);
                    if (!reader.IsDBNull(3))
                        d.Longitude = reader.GetString(3);
                    if (!reader.IsDBNull(4))
                        d.DiaChi = reader.GetString(4);
                    if (!reader.IsDBNull(5))
                        d.LinhVuc = reader.GetInt32(5);
                    if (!reader.IsDBNull(6))
                        d.Status = reader.GetInt32(6);
                    if (!reader.IsDBNull(7))
                        d.ChiTiet = reader.GetString(7);
                    list.Add(d);
                }
            }
            catch (Exception ex)
            {
                list = null;
            }
            finally
            {
                if (connect != null && connect.State == System.Data.ConnectionState.Open)
                    connect.Close();
            }
            return list;
        }
        #endregion

        #region lay dia diem theo ma
        public static DiaDiemDTO getDiaDiemById(int id)
        {
            SqlConnection connect = null;
            DiaDiemDTO d = new DiaDiemDTO();
            try
            {
                connect = DataProvider.Connect();
                string strCommand = "select * from diadiem where id=" + id;
                SqlCommand command = new SqlCommand(strCommand, connect);
                SqlDataReader reader = command.ExecuteReader();
                if (reader.Read())
                {
                    d.Id = reader.GetInt32(0);
                    if (!reader.IsDBNull(1))
                        d.Ten = reader.GetString(1);
                    if (!reader.IsDBNull(2))
                        d.Latitude = reader.GetString(2);
                    if (!reader.IsDBNull(3))
                        d.Longitude = reader.GetString(3);
                    if (!reader.IsDBNull(4))
                        d.DiaChi = reader.GetString(4);
                    if (!reader.IsDBNull(5))
                        d.LinhVuc = reader.GetInt32(5);
                    if (!reader.IsDBNull(6))
                        d.Status = reader.GetInt32(6);
                    if (!reader.IsDBNull(7))
                        d.ChiTiet = reader.GetString(7);
                }
            }
            catch (Exception ex)
            {
                d = null;
            }
            finally
            {
                if (connect != null && connect.State == System.Data.ConnectionState.Open)
                    connect.Close();
            }
            return d;
        }
        #endregion

        #region lay dia diem theo tai khoan
        public static List<DiaDiemDTO> getDiaDiemByAccount(int accountId, int share)
        {
            SqlConnection connect = null;
            List<DiaDiemDTO> list = new List<DiaDiemDTO>();
            try
            {
                connect = DataProvider.Connect();
                string strCommand = "select * from diadiem d,taikhoan_diadiem td, taikhoan t where t.id=td.taikhoanid and td.diadiemid=d.id and t.id=" + accountId;
                string add = " ";
                if (share == 0)
                {
                    add=" and td.share=0";
                }
                else if(share==1)
                {
                    add = " and td.share=1";
                }
                strCommand = strCommand + add;
                SqlCommand command = new SqlCommand(strCommand, connect);
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    DiaDiemDTO d = new DiaDiemDTO();
                    d.Id = reader.GetInt32(0);
                    if (!reader.IsDBNull(1))
                        d.Ten = reader.GetString(1);
                    if (!reader.IsDBNull(2))
                        d.Latitude = reader.GetString(2);
                    if (!reader.IsDBNull(3))
                        d.Longitude = reader.GetString(3);
                    if (!reader.IsDBNull(4))
                        d.DiaChi = reader.GetString(4);
                    if (!reader.IsDBNull(5))
                        d.LinhVuc = reader.GetInt32(5);
                    if (!reader.IsDBNull(6))
                        d.Status = reader.GetInt32(6);
                    if (!reader.IsDBNull(7))
                        d.ChiTiet = reader.GetString(7);
                    list.Add(d);
                }
            }
            catch (Exception ex)
            {
                list = null;
            }
            finally
            {
                if (connect != null && connect.State == System.Data.ConnectionState.Open)
                    connect.Close();
            }
            return list;
        }
        #endregion

        #region them dia diem
        public static bool insertDiaDiem(DiaDiemDTO d)
        {
            bool result = true;
            SqlConnection connect = null;
            try
            {
                connect = DataProvider.Connect();
                string strCommand = "insert into diadiem(ten, latitude, longitude, diachi, linhvuc, status, chitiet)"
                + " values(@ten, @latitude, @longitude, @diachi, @linhvuc,@status,@chitiet)";
                SqlCommand command = new SqlCommand(strCommand, connect);

                SqlParameter param;
                param = new SqlParameter("@ten", SqlDbType.VarChar);
                param.Value = d.Ten;
                command.Parameters.Add(param);

                param = new SqlParameter("@latitude", SqlDbType.VarChar);
                param.Value = d.Latitude;
                command.Parameters.Add(param);

                param = new SqlParameter("@longitude", SqlDbType.VarChar);
                param.Value = d.Longitude;
                command.Parameters.Add(param);

                param = new SqlParameter("@diachi", SqlDbType.VarChar);
                param.Value = d.DiaChi;
                command.Parameters.Add(param);

                param = new SqlParameter("@linhvuc", SqlDbType.Int);
                param.Value = d.LinhVuc;
                command.Parameters.Add(param);

                param = new SqlParameter("@status", SqlDbType.Int);
                param.Value = d.Status;
                command.Parameters.Add(param);

                param = new SqlParameter("@chitiet", SqlDbType.VarChar);
                param.Value = d.ChiTiet;
                command.Parameters.Add(param);

                int n = command.ExecuteNonQuery();
                if (n == 0)
                {
                    result = false;
                }
            }
            catch (Exception ex)
            {
                result = false;
            }
            finally
            {
                if (connect != null && connect.State == System.Data.ConnectionState.Open)
                    connect.Close();
            }
            return result;
        }
        #endregion

        #region cap nhat dia diem
        public static bool updateDiaDiem(DiaDiemDTO d)
        {
            bool result = true;
            SqlConnection connect = null;
            try
            {
                connect = DataProvider.Connect();
                string strCommand = @"update diadiem 
                                 set ten=@ten, latitude=@latitude, longitude=@longitude,
                                 diachi=@diachi, linhvuc= @linhvuc, status=@status, chitiet=@chitiet where id=@id";

                SqlCommand command = new SqlCommand(strCommand, connect);

                SqlParameter param;
                param = new SqlParameter("@id", SqlDbType.VarChar);
                param.Value = d.Id;
                command.Parameters.Add(param);

                param = new SqlParameter("@ten", SqlDbType.VarChar);
                param.Value = d.Ten;
                command.Parameters.Add(param);

                param = new SqlParameter("@latitude", SqlDbType.VarChar);
                param.Value = d.Latitude;
                command.Parameters.Add(param);

                param = new SqlParameter("@longitude", SqlDbType.VarChar);
                param.Value = d.Longitude;
                command.Parameters.Add(param);

                param = new SqlParameter("@diachi", SqlDbType.VarChar);
                param.Value = d.DiaChi;
                command.Parameters.Add(param);

                param = new SqlParameter("@linhvuc", SqlDbType.VarChar);
                param.Value = d.LinhVuc;
                command.Parameters.Add(param);

                param = new SqlParameter("@status", SqlDbType.Int);
                param.Value = d.Status;
                command.Parameters.Add(param);

                param = new SqlParameter("@chitiet", SqlDbType.VarChar);
                param.Value = d.ChiTiet;
                command.Parameters.Add(param);

                int n = command.ExecuteNonQuery();
                if (n == 0)
                {
                    result = false;
                }
            }
            catch (Exception ex)
            {
                result = false;
            }
            finally
            {
                if (connect != null && connect.State == System.Data.ConnectionState.Open)
                    connect.Close();
            }
            return result;
        }
        #endregion
    }
}
